﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PgBirdie.Core.Config;
using PgBirdie.Core.Extensions;
using PgBirdie.Core.Model;
using PgBirdie.Core.Model.Database.AccessControl;
using PgBirdie.Core.Model.Database.Structure;
using PgBirdie.Core.Model.Database.Structure.ColumnTypes.DateTime;
using PgBirdie.Core.Model.Database.Structure.ColumnTypes.Text;
using PgBirdie.Core.Model.Database.Structure.Constraint;
using PgBirdie.Core.Model.Database.Structure.Index;
using PgBirdie.Core.Service.Compare;

namespace PgBirdie.Core.Service.DbObject
{
    public class DbObjectToSqlService : IDbObjectToSqlService
    {
        private readonly ICompareService _compare;

        public DbObjectToSqlService(ICompareService compare)
        {
            _compare = compare;
        }     

        public string GetSchemaName(PgDbObject item)
        {
            if (item is PgTable)
                return String.Format("\"{0}\".", ((PgTable) item).Schema.Name);
            if (item is PgSequence)
                return String.Format("\"{0}\".", ((PgSequence)item).Schema.Name);
            return String.Empty;
        }

        public string CreateDatabase(PgDatabase db)
        {
            return String.Format("CREATE DATABASE \"{0}\";{1}", db.Name, Environment.NewLine);
        }

        public string CreateSchema(PgSchema schema)
        {
            return String.Format("CREATE SCHEMA \"{0}\";{1}", schema.Name, Environment.NewLine);
        }

        public string DropSchema(PgSchema schema)
        {
            return String.Format("DROP SCHEMA \"{0}\";{1}",
                                 schema.Name, Environment.NewLine);
        }

        public string CreateSchema(IList<PgSchema> schemas)
        {
            if (schemas.IsNullOrEmpty())
                return String.Empty;

            var sql = new StringBuilder();

            foreach (var schema in schemas)
                sql.AppendFormat("CREATE SCHEMA \"{0}\";{1}",
                                 schema.Name, Environment.NewLine);

            sql.Append(Environment.NewLine);

            return sql.ToString();
        }

        public string CreateTable(PgTable table)
        {
            if (table == null)
                return String.Empty;

            var sql = new StringBuilder();

            sql.AppendFormat("CREATE {0}TABLE {1}\"{2}\" ({3}",
                table.IsUnlogged ? "UNLOGGED " : String.Empty,
                GetSchemaName(table),
                table.Name,
                Environment.NewLine);

            IList<string> columnDefs = new List<string>();
            foreach (var column in table.Columns)
                columnDefs.Add(GetSqlForTableColumn(column.Value));

            sql.Append(String.Join(", " + Environment.NewLine, columnDefs.ToArray()));
            sql.Append(Environment.NewLine);
            sql.AppendFormat(");{0}", Environment.NewLine);

            return sql.ToString();
        }        

        public string DropTable(PgTable table)
        {
            if (table == null)
                return String.Empty;

            return String.Format("DROP TABLE {0}\"{1}\";{2}",
                GetSchemaName(table),
                table.Name,
                Environment.NewLine);
        }

        public string CreateSequence(PgSequence sequence)
        {
            return String.Format(
                "CREATE SEQUENCE \"{7}\".\"{1}\"{0}" +
                "START WITH {2}{0}" + 
                "INCREMENT BY {3}{0}" +
                "MINVALUE {4}{0}" + 
                "MAXVALUE {5}{0}" +                 
                "CACHE {6};{0}",                
                Environment.NewLine, sequence.Name, sequence.StartWith, sequence.IncrementBy, sequence.MinValue, sequence.MaxValue, sequence.Cache, sequence.Schema.Name);
        }

        public string AssociateTableColumnWithSequence(PgColumn column)
        {
            // Example:
            // ALTER TABLE "TableName" ALTER COLUMN "Id" SET DEFAULT nextval('"SEQ_TableName"'::regclass);

            if (column == null || column.SequenceName == null)
                return String.Empty;

            var sequenceName = column.SequenceName;

            return String.Format("ALTER TABLE {0}\"{1}\" ALTER COLUMN \"{2}\" SET DEFAULT nextval('{3}'::regclass);{4}",
                                 GetSchemaName(column.Table),
                                 column.Table.Name,
                                 column.Name,
                                 sequenceName.Any(c => char.IsUpper(c)) ? String.Format("\"{0}\"", sequenceName) : sequenceName,
                                 Environment.NewLine);
        }

        public string CreateIndex(PgIndex index)
        {
            var createIndex = String.Format("CREATE {0}INDEX \"{1}\" ON {2}\"{3}\" USING btree ({4});{5}",
                                            index.IsUnique ? "UNIQUE " : String.Empty,
                                            index.Name,
                                            GetSchemaName(index.Table),
                                            index.Table.Name,
                                            GetSqlForIndexColumns(index.Columns),
                                            Environment.NewLine);

            var clusterIndex = String.Empty;
            if (index.IsClustered)
                clusterIndex = String.Format("CLUSTER {0}\"{1}\" USING \"{2}\";{3}",
                                             GetSchemaName(index.Table),
                                             index.Table.Name,
                                             index.Name,
                                             Environment.NewLine);

            return String.Format("{0}{1}", createIndex, clusterIndex);
        }

        public string AlterIndex(PgIndex source, PgIndex target)
        {
            throw new NotImplementedException("AlterIndex is not supported yet.");
        }

        public string DropIndex(PgIndex index)
        {
            if (index == null)
                return String.Empty;

            return String.Format("DROP INDEX {0}\"{1}\";{2}",
                GetSchemaName(index.Table),
                index.Name,
                Environment.NewLine);
        }

        public string AddColumn(PgColumn column)
        {
            var sql = String.Format("ALTER TABLE {0}\"{1}\" ADD COLUMN \"{2}\" {3}{4};{5}",
                                    GetSchemaName(column.Table),
                                    column.Table.Name,
                                    column.Name,
                                    GetSqlForColumnType(column),
                                    column.IsNullable ? String.Empty : " NOT NULL",
                                    Environment.NewLine);

            if (column.SequenceName != null)
                sql += AssociateTableColumnWithSequence(column);

            return sql;
        }

        public string AlterColumn(PgColumn source, PgColumn target)
        {
            var columnTypePropertiesMatch = _compare.ColumnTypePropertiesMatch(source, target);
            var columnIsNullableMatch = _compare.ColumnIsNullableMatch(source, target);
            var columnSequenceMatch = _compare.ColumnSequenceMatch(source, target);

            var alterType = String.Empty;
            if (!columnTypePropertiesMatch)
                alterType = String.Format("ALTER TABLE {0}\"{1}\" ALTER COLUMN \"{2}\" TYPE {3};{4}",
                                          GetSchemaName(source.Table),
                                          source.Table.Name,
                                          source.Name,
                                          GetSqlForColumnType(source),
                                          Environment.NewLine);

            var alterNull = String.Empty;
            if (!columnIsNullableMatch)
                alterNull = String.Format("ALTER TABLE {0}\"{1}\" ALTER COLUMN \"{2}\" {3} NOT NULL;{4}",
                                          GetSchemaName(source.Table),
                                          source.Table.Name,
                                          source.Name,
                                          source.IsNullable ? "DROP" : "SET",
                                          Environment.NewLine);

            var alterSequence = String.Empty;
            if (!columnSequenceMatch)
                alterSequence = AssociateTableColumnWithSequence(source);

            return String.Format("{0}{1}{2}", alterType, alterNull, alterSequence);
        }

        public string DropColumn(PgColumn column)
        {
            return String.Format("ALTER TABLE {0}\"{1}\" DROP COLUMN \"{2}\";{3}",
                                 GetSchemaName(column.Table),
                                 column.Table.Name,
                                 column.Name,
                                 Environment.NewLine);
        }

        public string AddConstraint(PgConstraintBase constraint)
        {
            switch (constraint.ConstraintType)
            {
                case ConstraintType.PrimaryKey:
                    return AddPrimaryKey((PgPrimaryKey)constraint);
                case ConstraintType.UniqueKey:
                    return AddUniqueKey((PgUniqueKey)constraint);
                case ConstraintType.ForeignKey:
                    return AddForeignKey((PgForeignKey)constraint);
                default:
                    throw new Exception(String.Format("Unknown constraint type: {0}.", constraint.ConstraintType));
            }                
        }

        public string AddPrimaryKey(PgPrimaryKey key)
        {
            if (key == null)
                return String.Empty;

            return String.Format("ALTER TABLE {0}\"{1}\" ADD CONSTRAINT \"{2}\" PRIMARY KEY (\"{3}\");{4}",
                                 GetSchemaName(key.Table),
                                 key.Table.Name,
                                 key.Name,
                                 String.Join("\", \"", key.ColumnNames.ToArray()),
                                 Environment.NewLine);
        }

        public string AddUniqueKey(PgUniqueKey key)
        {
            if (key == null)
                return String.Empty;

            return String.Format("ALTER TABLE {0}\"{1}\" ADD CONSTRAINT \"{2}\" UNIQUE (\"{3}\");{4}",
                                 GetSchemaName(key.Table),
                                 key.Table.Name,
                                 key.Name,
                                 String.Join("\", \"", key.ColumnNames.ToArray()),
                                 Environment.NewLine);
        }

        public string AddForeignKey(PgForeignKey key)
        {
            if (key == null)
                return String.Empty;

            return
                String.Format(
                    "ALTER TABLE {0}\"{1}\" ADD CONSTRAINT \"{2}\" FOREIGN KEY (\"{3}\") REFERENCES {8}\"{4}\" (\"{5}\") MATCH {6};{7}",
                    GetSchemaName(key.Table),
                    key.Table.Name,
                    key.Name,
                    key.SourceColumnName,
                    key.ReferenceTableName,
                    key.ReferenceColumnName,
                    key.MatchType,
                    Environment.NewLine,
                    GetSchemaName(key.Table));
        }

        public string AddCheck(PgCheck check)
        {
            if (check == null)
                return string.Empty;

            return string.Format(
                "ALTER TABLE {0}\"{1}\" ADD CONSTRAINT \"{2}\" CHECK (\"{3}\" {4} {5});{6}",
                GetSchemaName(check.Table),
                check.Table.Name,
                check.Name,
                check.ColumnName,
                check.Operation,
                check.Expression,
                Environment.NewLine);
        }

        public string DropConstraint(PgConstraintBase key)
        {
            if (key == null)
                return String.Empty;

            return String.Format("ALTER TABLE {0}\"{1}\" DROP CONSTRAINT \"{2}\";{3}",
                                 GetSchemaName(key.Table),
                                 key.Table.Name,
                                 key.Name,
                                 Environment.NewLine);
        }

        public string SetOwner(PgOwner owner)
        {
            if (owner == null)
                return String.Empty;

            return String.Format("ALTER {0} {1}\"{2}\" OWNER TO \"{3}\";{4}",
                                 owner.Target.ModelType.ToString().ToUpper(),
                                 GetSchemaName(owner.Target),
                                 owner.Target.Name,
                                 owner.Role.Name,
                                 Environment.NewLine);
        }

        public string SetPrivilege(PgPrivilege privilege)
        {
            if (privilege == null)
                return String.Empty;

            return String.Format("{0} {1} ON {2} {3}\"{4}\" {5} {6};{7}",
                                 privilege.CommandType.ToString().ToUpper(),
                                 String.Join(",", privilege.Types.Select(c => c.ToString().ToUpper()).ToArray()),
                                 privilege.Target.ModelType.ToString().ToUpper(),
                                 GetSchemaName(privilege.Target),
                                 privilege.Target.Name,
                                 privilege.CommandType == PgPrivilegeCommandType.Grant ? "TO" : "FROM",
                                 privilege.Role.Name.Equals("public", StringComparison.OrdinalIgnoreCase)
                                     ? "PUBLIC"
                                     : String.Format("\"{0}\"", privilege.Role.Name),
                                 Environment.NewLine);
        }        

        private string GetSqlForTableColumn(PgColumn column)
        {
            if (column == null)
                throw new Exception("Column cannot be null at this point.");

            return String.Format("    \"{0}\" {1}{2}",
                                 column.Name,
                                 GetSqlForColumnType(column),                                 
                                 column.IsNullable ? String.Empty : " NOT NULL");
        }

        private string GetSqlForColumnType(PgColumn column)
        {
            var baseType = column.Type;

            if (column.Type.AsEnum == ColumnDataType.Timestamp)
            {
                var type = ((PgTimestamp)baseType);
                return String.Format("{0}({1}) {2} time zone",
                                     type.Name,
                                     type.Precision,
                                     type.WithTimeZone ? "with " : "without");
            }

            if (column.Type.AsEnum == ColumnDataType.Char || column.Type.AsEnum == ColumnDataType.VarChar)
            {
                var type = ((PgCharacterBase)baseType);
                return String.Format("{0}({1})", type.Name, type.Length);
            }

            return String.Format("{0}", baseType.Name);
        }

        private string GetSqlForIndexColumns(IEnumerable<PgIndexColumn> columns)
        {
            var sb = new StringBuilder();

            foreach (var column in columns)
            {
                var name = column.Name;
                var order = column.IsDescending ? "DESC" : "ASC";
                var nulls = column.NullsFirst ? "FIRST" : "LAST";

                if (!string.IsNullOrWhiteSpace(column.Function))
                    sb.AppendFormat("{0}(\"{1}\") {2} NULLS {3}", column.Function, name, order, nulls);
                else
                    sb.AppendFormat("\"{0}\" {1} NULLS {2}", name, order, nulls);
            }

            return sb.ToString();
        }
    }
}